show engines;
select version();

# 事务是对一组SQL的管理，共存亡
select * from users;
- 预存1000 

-- 组---
-- 积分表 -> 1000积分
-- 会员周期表 -> 张三 到期2030 出问题
-- 提交

-- 事务==组
-- 发表视频评论
-- 视频 -> 评论数 202

begin;
insert into users set name ='小爱@@';
select * from users;
rollback;

# commit后的SQL又会变成立即提交
begin;
INSERT  into users set name = '李华###';
SELECT * from users;
rollback;

begin;
SELECT * from users;
UPDATE users set sex = '男' where id=1;
commit;

set autocommit =0;
UPDATE users set sex = '男' where id=2;
commit;

-- DB::autocommit;
-- .... 全局
-- DB::commit;

-- DB::transtion(function(){
-- 	try{
-- 	....
-- 	...
-- 	...
-- 		if(accect('admin')){
-- 			throw new Exception('权限不够');
-- 		}
-- 	}catch(Exception $e){
-- 		DB::rollback();
-- 	}
-- })

select @@global.transaction_isolation,@@transaction_isolation;

set autocommit =1;

set session transaction isolation 
level serializable;

begin;
update users set sex ='女' where id=2;
select count(*) from users;
update users set group_id =2 ;

select * from users;
-- 16 17
commit;
set session transaction isolation level REPEATABLE READ;

begin;
SELECT * from users;
create table goods 
(id int primary key auto_increment,name char(30),nums int default 100);
insert into goods set name = 'IPAD';
commit ;
select * from goods;
-- IPAD 100  1000
-- 2分钟


begin;
select * from goods where id=1 for UPDATE ;
update goods set nums = nums-10 where id=1;
select * from goods;
commit;

lock table goods write;

# 后台框架

ALTER TABLE users ADD 
CONSTRAINT users_groups
FOREIGN KEY (group_id) 
REFERENCES `groups`(id) 
ON DELETE CASCADE;


DELIMITER $$
CREATE PROCEDURE add_stus(IN num int)
BEGIN 
DECLARE i int DEFAULT 0;
DECLARE _birthday datetime;
WHILE num>i DO
    SET _birthday  = date_sub(now(),INTERVAL floor(RAND()*1000) day);
    INSERT INTO stu SET 
    sname = concat(left(md5(RAND()),5),'后盾人向军'),
    class_id =1+FLOOR( RAND()*100000),
    birthday = _birthday ,
    sex = 1+FLOOR(RAND()*2 );

    SET i= i+1;
END WHILE;
END 
$$

DELIMITER ;


call add_stus(500000);















